﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Data.SqlClient;
using ClassModel;


namespace ClassDAL
{
    public class t_classDemo_UserInfo_DAL
    {

        private const string t_TableName = "t_classDemo_UserInfo";

        private const string f_id = "id";
        private const string f_account = "userAccount";
        private const string f_password = "userPassword";
        private const string f_createTime = "createTime";


        #region 公开方法
        /// <summary>
        /// 用户注册
        /// </summary>
        /// <param name="account">用户账户信息</param>
        /// <returns>返回添加的用户ID</returns>
        public static int InsertAccount(t_classDemo_UserInfo account)
        {
            if (account == null) return 0;
            if (checkUserExists(account.userAccount)) return 0;

            string insertCommand = "INSERT INTO t_classDemo_UserInfo (userAccount,userPassword,createTime) VALUES(@userAccount,@userPassword,@createTime); SELECT @@IDENTITY;";
            SqlParameter[] ps = { 
                                new SqlParameter("@userAccount",account.userAccount),
                                new SqlParameter(@"userPassword",account.userPassword),
                                new SqlParameter(@"createTime",DateTime.Now)
                                };

            return Convert.ToInt32(SQLHelper.ExecuteScalar(SQLHelper.GetConnection(), System.Data.CommandType.Text, insertCommand, ps));
        }


        /// <summary>
        /// 根据id查询用户
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        public static t_classDemo_UserInfo GetUserInfoById(int id)
        {
            string selectSQL = string.Format("SELECT {0},{1},{2},{3} FROM {4} WHERE id = {5} ;", f_id, f_account, f_password, f_createTime, t_TableName, id);

            SqlDataReader reader = SQLHelper.ExecuteReader(SQLHelper.GetConnSting(), System.Data.CommandType.Text, selectSQL);
            if (!reader.Read()) return null;

            t_classDemo_UserInfo userInfo = new t_classDemo_UserInfo();
            userInfo.id = Convert.ToInt32(reader[f_id]);
            userInfo.userPassword = reader[f_password].ToString();
            userInfo.userAccount = reader[f_account].ToString();
            userInfo.createTime = Convert.ToDateTime(reader[f_createTime]);
            reader.Close();
            return userInfo;
        }


        public static int Login(string userName, string password)
        {
            string loginSQL = "SELECT id FROM t_classDemo_UserInfo WHERE userAccount = @userAccount AND userPassword = @userPassword;";
            SqlParameter[] loginParams = new SqlParameter[] 
            {
                new SqlParameter("@userAccount",userName),
                new SqlParameter("@userPassword",password)
            };

            var obj = SQLHelper.ExecuteScalar(SQLHelper.GetConnection(), System.Data.CommandType.Text, loginSQL, loginParams);
            return obj == null ? -1 : Convert.ToInt32(obj);
        }

        #endregion 公开方法结束

        #region 测试需要的方法
        /// <summary>
        /// 删除指定账户信息
        /// </summary>
        /// <param name="account"></param>
        /// <returns></returns>
        public static int DeleteWithAccount(string account)
        {
            string deleteSQL = "delete from t_classDemo_UserInfo where userAccount = @account;";
            SqlParameter deleteParam = new SqlParameter() 
            {
                ParameterName = "@account",
                Value = account
            };

            return SQLHelper.ExecuteNonQuery(SQLHelper.GetConnection(), System.Data.CommandType.Text, deleteSQL, deleteParam);
        }
        #endregion

        #region 用户找回密码
        /// <summary>
        /// 用户找回密码
        /// </summary>
        /// <param name="newPassword">新密码</param>
        /// <param name="userAccount">修改的账户</param>
        /// <returns></returns>
        public static bool UpdatePassword(string newPassword, string userAccount)
        {
            // 账号不存在
            if (!checkUserExists(userAccount)) return false;


            string updatePasswordSQL = "update t_classDemo_UserInfo set userPassword = @newPassword where userAccount = @userAccount;";
            SqlParameter[] updateParams = new SqlParameter[] 
            {
                new SqlParameter("@newPassword",newPassword),
                new SqlParameter("@userAccount",userAccount)
            };

            return SQLHelper.ExecuteNonQuery(SQLHelper.GetConnection(), System.Data.CommandType.Text, updatePasswordSQL, updateParams) > 0;
        }
        #endregion

        #region 用户账号是否存在
        /// <summary>
        /// 检查当前账号是否存在
        /// </summary>
        /// <param name="userAccount"></param>
        /// <returns></returns>
        public static bool checkUserExists(string userAccount)
        {
            string checkSql = "SELECT ID FROM t_classDemo_UserInfo WHERE userAccount = @userAccount;";
            SqlParameter ps = new SqlParameter("@userAccount", userAccount);

            return SQLHelper.ExecuteScalar(SQLHelper.GetConnection(), System.Data.CommandType.Text, checkSql, ps) != null;
        }

        #endregion





    }
}
